Sorting MYSQL results Paging and column heads

am 05.10.2009 20:57:42 von Edward Brookhouse

Hi All,

I am working on a PHP page to display results from a MySQL db and I am havi=
ng a problem getting my results to page properly.

When the page loads, it looks fine, but hitting next/previous does not chan=
ge the data?

Any thoughts appreciated -

Edward
Ebrooathealthydirectionsdotcom

w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

tf-8" />

include_once 'config/constants.php';
include_once 'config/opendb.php';
// rows to return
$rowsPerPage =3D 20;
$pageNum =3D 1;
$yesterday =3D strftime ("%Y/%m/%d", strtotime("-2 day"));
$pretty =3D strftime ("%A %B %d");
$page =3D $_GET['page'];

print " ' cellpadding=3D5 cellspacing=3D1 bgcolor=3DFFFFFF width=3D\"1009\" height=
=3D\"293\" border=3D\"0\">";
print " ";
print "";
print "";
print "
e.healthydirections.com/images/hd_bnr_TLv2.gif\" width=3D\"471\" height=3D\=
"123\" />
";
print "
Bounce Report
";
print " directions.com/bounce\">Go to Adaptive Log
";

print "
";
print "Showing results from $pretty
";
print "
";
print " ";
print "
";
print "

";

if(isset($_GET['page']))
{
$pageNum =3D $_GET['page'];
}

$offset =3D ($pageNum - 1) * $rowsPerPage;

db_connect_ecelerity(DBUSER, DBUSERPW);
$query1 =3D "select * FROM `bouncelog` ORDER BY `insert_date` DESC LIMIT $o=
ffset, $rowsPerPage";
$numresults=3Dmysql_query($query1);
$numrows=3Dmysql_num_rows($numresults);
$result =3D mysql_query($query1) or die('Error, lame query failed');

function makeHeaderLink($value, $key, $col, $dir) {
$out =3D " "?c=3D";
//set column query string value
switch($key) {
case "insert_date":
$out .=3D "1";
break;
case "message_id":
$out .=3D "2";
break;
case "log_type":
$out .=3D "3";
break;
case "to_local":
$out .=3D "4";
break;
case "to_domain":
$out .=3D "5";
break;
case "from_local":
$out .=3D "6";
break;
case "from_domain":
$out .=3D "7";
break;
case "binding_group":
$out .=3D "8";
break;
case "binding_name":
$out .=3D "9";
break;
case "bounce_phase":
$out .=3D "10";
break;
case "bounce_code":
$out .=3D "11";
break;
case "ip_address":
$out .=3D "12";
break;
case "bounce_message":
$out .=3D "13";
break;
default:
$out .=3D "1";
}

$out .=3D "&d=3D";

//reverse sort if the current column is clicked
if($key == $col) {
switch($dir) {
case "ASC":
$out .=3D "1";
break;
default:
$out .=3D "0";
}
}
else {
//pass on current sort direction
switch($dir) {
case "ASC":
$out .=3D "0";
break;
default:
$out .=3D "1";
}
}

//complete link
$out .=3D "\">$value";

return $out;
}

switch($_GET['c']) {
case "1":
$col =3D "insert_date";
break;
case "3":
$col =3D "message_id";
break;
case "3":
$col =3D "log_type";
break;
case "4":
$col =3D "to_local";
break;
case "5":
$col =3D "to_domain";
break;
case "6":
$col =3D "from_local";
break;
case "7":
$col =3D "from_domain";
break;
case "8":
$col =3D "binding_group";
break;
case "9":
$col =3D "binding_name";
break;
case "10":
$col =3D "binding_phase";
break;
case "11":
$col =3D "bounce_code";
break;
case "12":
$col =3D "ip_address";
break;
case "13":
$col =3D "bounce_message";
break;
default:
$col =3D "insert_date";
}

if($_GET['d'] == "1") {
$dir =3D "DESC";
}
else {
$dir =3D "ASC";
}

if(!$link =3D mysql_connect("localhost", "root", "psss")) {
echo "Cannot connect to db server";
}
elseif(!mysql_select_db("ecelerity")) {
echo "Cannot select database";
}
else {
if(!$rs =3D mysql_query("SELECT * FROM bouncelog ORDER BY $col =
$dir LIMIT 500")) {
echo "Cannot parse query";
}
elseif(mysql_num_rows($rs) == 0) {
echo "No records found";
}
else {

echo "\n";

echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "";
echo "\n";

for($i =3D 0; $i < $numrows; $i++)
{
$row =3D mysql_fetch_array($rs); //get a row from our resul=
t set
$date2 =3D $row['insert_date'];
$date3 =3D date("F-d-Y", $date2);

$ip2 =3D $row['ip_address'];
$host =3D long2ip($ip2);

if(($i % 2) == 0) {
echo "\n";
} else {
echo "\n";
}

echo "<
td>".$row['from_domain']."";
echo "\n";
}
echo "
";
}
}

$query =3D "SELECT COUNT(insert_date) AS numrows FROM bouncelog";
$result =3D mysql_query($query) or die('Error, query lamefailed');
$row =3D mysql_fetch_array($result, MYSQL_ASSOC);
$numrows =3D $row['numrows'];

// how many pages we have when using paging?
$maxPage =3D ceil($numrows/$rowsPerPage);

$self =3D $_SERVER['PHP_SELF'];

if ($pageNum > 1)
{
$page =3D $pageNum - 1;
$prev =3D " ";

$first =3D " ";
}
else
{
$prev =3D ' [Prev] '; // we're on page one, don't enable 'previo=
us' link
$first =3D ' [First Page] '; // nor 'first page' link
}

if ($pageNum < $maxPage)
{
$page =3D $pageNum + 1;
$next =3D " ";

$last =3D " ";
}
else
{
$next =3D ' [Next] '; // we're on the last page, don't enable 'nex=
t' link
$last =3D ' [Last Page] '; // nor 'last page' link
}

echo $first . $prev . " Showing page $pageNum of $=
maxPage pages " . $next . $last;

echo "

" . makeHeaderLink("Date", "insert_date", $col, $= dir) . "	" . makeHeaderLink("Message ID", "message_id", $c= ol, $dir) . "	" . makeHeaderLink("Log Type", "log_type", $col, = $dir) . "	" . makeHeaderLink("To Local", "to_local", $col, = $dir) . "	" . makeHeaderLink("To Domain", "to_domain", $col= , $dir) . "	" . makeHeaderLink("From Local", "from_local", $c= ol, $dir) . "	" . makeHeaderLink("From Domain", "from_domain", = $col, $dir) . "	" . makeHeaderLink("Binding Group", "binding_grou= p", $col, $dir) . "	" . makeHeaderLink("Binding Name", "binding_name"= , $col, $dir) . "	" . makeHeaderLink("Bounce Phase", "bounce_phase"= , $col, $dir) . "	" . makeHeaderLink("Bounce Code", "bounce_code", = $col, $dir) . "	" . makeHeaderLink("IP Address", "ip_address", $c= ol, $dir) . "	" . makeHeaderLink("Bounce Message", "bounce_mess= age", $col, $dir) . "
$date3	".$row['message_id']."	".$row['log_type'].= "	".$row['to_local']."	".$row['to_domain']."	".$row= ['from_local']."	".$row['binding_group']."	".$row= ['binding_name']."	".$row['bounce_phase']."	".$row['bounce_= code']."	".$ro w['ip_address']."	".$row['bounce_message']."

";
?>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Sorting MYSQL results Paging and column heads

am 06.10.2009 00:11:10 von dmagick

Edward Brookhouse wrote:
> Hi All,
>
> I am working on a PHP page to display results from a MySQL db and I am having a problem getting my results to page properly.
>
> When the page loads, it looks fine, but hitting next/previous does not change the data?

> db_connect_ecelerity(DBUSER, DBUSERPW);
> $query1 = "select * FROM `bouncelog` ORDER BY `insert_date` DESC LIMIT $offset, $rowsPerPage";

Is it running this query or the next one?

> if(!$rs = mysql_query("SELECT * FROM bouncelog ORDER BY $col $dir LIMIT 500")) {
> echo "Cannot parse query";

There's only a limit here, no offset attached.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Sorting MYSQL results Paging and column heads

e.healthydirections.com/images/hd_bnr_TLv2.gif\" width=3D\"471\" height=3D\= "123\" />

Bounce Report

Re: Sorting MYSQL results Paging and column heads

e.healthydirections.com/images/hd_bnr_TLv2.gif\" width=3D\"471\" height=3D\=
"123\" />